const XLSX = require('xlsx');
const fs = require('fs').promises;
const path = require('path');
const { pool, query: dbQuery } = require('../config/database');
const { logger } = require('../utils/logger');
const MenuItem = require('../models/MenuItem');
const MenuCategory = require('../models/MenuCategory');
const ImportJob = require('../models/ImportJob');
const MenuValidation = require('../utils/menuValidation');
const { SCHEMA, RULES, exactHeaderMatch, slugify, parseBoolean, normalizeBaseStatus, normalizePopularity } = require('../constants/importV2Schema');

/**
 * 菜单导入服务类
 * 处理Excel文件解析、数据校验、规范化和导入逻辑
 */
class MenuImportService {
  constructor() {
    this.validationRules = {
      required_fields: ['name_en', 'category'],
      max_length: {
        name_en: 200,
        name: 200,
        description: 5000,
        main_ingredients: 1000
      },
      price_range: {
        min: 0,
        max: 9999.99
      }
    };
  }

  /**
   * 解析Excel文件
   */
  async parseExcelFile(filePath) {
    try {
      logger.info(`开始解析Excel文件: ${filePath}`);
      
      // 检查文件是否存在
      const fileExists = await fs.access(filePath).then(() => true).catch(() => false);
      if (!fileExists) {
        throw new Error('文件不存在');
      }

      // 读取Excel文件
      const workbook = XLSX.readFile(filePath);
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      
      // 转换为JSON数据
      const rawData = XLSX.utils.sheet_to_json(worksheet, { 
        header: 1,
        defval: '',
        blankrows: false
      });

      if (rawData.length < 2) {
        throw new Error('Excel文件格式错误：至少需要标题行和一行数据');
      }

      // 获取标题行
      const headers = rawData[0];
      const dataRows = rawData.slice(1);

      // 验证必要的列是否存在
      const requiredColumns = ['Name_English', 'Category'];
      const missingColumns = requiredColumns.filter(col => !headers.includes(col));
      if (missingColumns.length > 0) {
        throw new Error(`缺少必要的列: ${missingColumns.join(', ')}`);
      }

      // 转换为对象数组
      const parsedData = dataRows.map((row, index) => {
        const rowData = { _row_number: index + 2 }; // Excel行号从2开始
        headers.forEach((header, colIndex) => {
          rowData[header] = row[colIndex] || '';
        });
        return rowData;
      }).filter(row => {
        // 过滤空行（至少要有Name_English或菜品名称）
        return row['Name_English'] || row['菜品名称（中文）'];
      });

      logger.info(`Excel解析完成，共${parsedData.length}行有效数据`);
      
      return {
        headers,
        data: parsedData,
        total_rows: parsedData.length
      };
    } catch (error) {
      logger.error('Excel文件解析失败:', error);
      throw error;
    }
  }

  /**
   * 数据规范化函数（使用MenuValidation工具类）
   */
  normalize(str) {
    return MenuValidation.normalize(str);
  }

  /**
   * 数据清洗函数（使用MenuValidation工具类）
   */
  cleanData(value) {
    return MenuValidation.cleanData(value);
  }

  /**
   * 验证单行数据（使用MenuValidation工具类）
   */
  async validateRow(rowData, existingCategories, existingItems) {
    const rowNumber = rowData._row_number;
    
    // 使用MenuValidation进行基础验证
    const validationResult = MenuValidation.validateRow(rowData, existingItems, rowNumber - 2, {
      requiredFields: ['Name_English', 'Category'],
      lengthRules: {
        'Name_English': 200,
        '菜品名称（中文）': 200,
        'Item Description（中文）': 5000,
        'Main Ingredients（中文）': 1000,
        'Category': 100,
        'Quantity Selection': 500
      },
      priceRange: {
        min: 0,
        max: 9999.99
      }
    });
    
    const errors = validationResult.errors.map(msg => ({
      row_number: rowNumber,
      field: 'general',
      value: '',
      error_code: 'VALIDATION_ERROR',
      message: msg
    }));
    
    const warnings = validationResult.warnings.map(msg => ({
      row_number: rowNumber,
      field: 'general',
      value: '',
      warning_code: 'VALIDATION_WARNING',
      message: msg
    }));
    
    // 额外的业务逻辑验证 - 分类存在性
    let category = this.cleanData(rowData['Category']);
    if (category) {
      const trimmed = String(category).trim();
      if (trimmed !== category) {
        category = trimmed;
        // 无 normalizationLog 可用于旧流程，这里仅修正值
      }
      if (!existingCategories.some(cat => 
        cat.name === category || cat.name_en === category
      )) {
        // 旧流程也不再报错，统一提示将自动创建
        warnings.push({
          row_number: rowNumber,
          field: 'Category',
          value: category,
          warning_code: 'CATEGORY_WILL_BE_CREATED',
          message: `分类 ${category} 不存在，将自动创建`
        });
      }
    }

    return { errors, warnings };
  }

  /**
   * 检查重复数据
   */
  checkDuplicates(data) {
    const errors = [];
    const nameEnMap = new Map();

    data.forEach(row => {
      const nameEn = this.normalize(this.cleanData(row['Name_English']));
      if (nameEn) {
        if (nameEnMap.has(nameEn)) {
          const duplicateRows = nameEnMap.get(nameEn);
          duplicateRows.push(row._row_number);
          
          errors.push({
            row_number: row._row_number,
            field: 'Name_English',
            value: row['Name_English'],
            error_code: 'DUPLICATE_NAME',
            message: `英文名称重复，重复行号: ${duplicateRows.join(', ')}`
          });
        } else {
          nameEnMap.set(nameEn, [row._row_number]);
        }
      }
    });

    return errors;
  }

  /**
   * 执行Dry-Run验证
   */
  async performDryRun(job) {
    try {
      logger.info(`开始执行Dry-Run验证，任务ID: ${job.job_id}`);
      
      await job.updateStatus('validating');

      // 解析Excel文件
      const parseResult = await this.parseExcelFile(job.file_path);
      const { data, total_rows } = parseResult;

      // 获取现有数据
      const existingCategories = await MenuCategory.findAll({ is_active: true });
      const existingItems = await MenuItem.findAll();

      let allErrors = [];
      let allWarnings = [];
      let validRows = 0;
      let newRecords = 0;
      let updatedRecords = 0;

      // 检查文件内重复
      const duplicateErrors = this.checkDuplicates(data);
      allErrors = allErrors.concat(duplicateErrors);

      // 逐行验证
      for (const row of data) {
        const { errors, warnings } = await this.validateRow(row, existingCategories, existingItems);
        allErrors = allErrors.concat(errors);
        allWarnings = allWarnings.concat(warnings);

        if (errors.length === 0) {
          validRows++;
          
          // 检查是新建还是更新
          const nameEn = this.normalize(this.cleanData(row['Name_English']));
          const existingItem = existingItems.find(item => 
            item.id === parseInt(row['ID']) || 
            this.normalize(item.name_en) === nameEn
          );
          
          if (existingItem) {
            updatedRecords++;
          } else {
            newRecords++;
          }
        }
      }

      // 生成错误文件
      let errorFilePath = null;
      if (allErrors.length > 0) {
        errorFilePath = await this.generateErrorFile(job.job_id, allErrors);
      }

      const result = {
        total_rows,
        valid_rows: validRows,
        error_rows: total_rows - validRows,
        warning_rows: allWarnings.length,
        new_records: newRecords,
        updated_records: updatedRecords,
        validation_errors: allErrors,
        warnings: allWarnings,
        error_file_path: errorFilePath
      };

      await job.saveDryRunResult(result);
      
      logger.info(`Dry-Run验证完成，任务ID: ${job.job_id}，有效行数: ${validRows}/${total_rows}`);
      
      return result;
    } catch (error) {
      logger.error('Dry-Run验证失败:', error);
      await job.updateStatus('failed', { error_message: error.message });
      throw error;
    }
  }

  /**
   * 生成错误文件
   */
  async generateErrorFile(jobId, errors) {
    try {
      const errorDir = path.join(process.cwd(), 'uploads', 'errors');
      await fs.mkdir(errorDir, { recursive: true });
      
      const errorFilePath = path.join(errorDir, `errors_${jobId}.csv`);
      
      // 生成CSV内容
      const csvHeader = 'row_number,sheet,field,value,error_code,message\n';
      const csvRows = errors.map(error => {
        return `${error.row_number},Sheet1,"${error.field}","${error.value}",${error.error_code},"${error.message}"`;
      }).join('\n');
      
      const csvContent = csvHeader + csvRows;
      await fs.writeFile(errorFilePath, csvContent, 'utf8');
      
      return errorFilePath;
    } catch (error) {
      logger.error('生成错误文件失败:', error);
      throw error;
    }
  }

  /**
   * 提交导入数据
   */
  async commitImport(job) {
    const connection = await require('../config/database').getConnection();
    
    try {
      logger.info(`开始提交导入数据，任务ID: ${job.job_id}`);
      
      await job.updateStatus('committing');
      await connection.beginTransaction();

      // 创建导入前快照
      const snapshot = await this.createSnapshot(connection);
      await job.saveSnapshot(snapshot);

      // 解析Excel文件
      const parseResult = await this.parseExcelFile(job.file_path);
      const { data } = parseResult;

      // 获取现有分类
      const existingCategories = await MenuCategory.findAll({ is_active: true });
      const categoryMap = new Map();
      existingCategories.forEach(cat => {
        categoryMap.set(cat.name, cat.id);
        categoryMap.set(cat.name_en, cat.id);
      });

      let processedCount = 0;

      for (const row of data) {
        // 跳过有错误的行
        const { errors } = await this.validateRow(row, existingCategories, []);
        if (errors.length > 0) {
          continue;
        }

        await this.processRow(connection, row, categoryMap);
        processedCount++;
      }

      await connection.commit();
      await job.updateStatus('completed', { 
        processed_records: processedCount 
      });
      
      logger.info(`导入数据提交完成，任务ID: ${job.job_id}，处理记录数: ${processedCount}`);
      
      return { processed_records: processedCount };
    } catch (error) {
      await connection.rollback();
      logger.error('提交导入数据失败:', error);
      await job.updateStatus('failed', { error_message: error.message });
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 处理单行数据
   */
  async processRow(connection, row, categoryMap) {
    try {
      // 清洗数据
      const cleanedData = {
        id: row['ID'] ? parseInt(row['ID']) : null,
        name_en: this.cleanData(row['Name_English']),
        name: this.cleanData(row['菜品名称（中文）']),
        description: this.cleanData(row['Item Description（中文）']),
        main_ingredients: this.cleanData(row['Main Ingredients（中文）']),
        quantity_selection: this.cleanData(row['Quantity Selection']),
        price: parseFloat(row['Price']) || 0,
        category: this.cleanData(row['Category']),
        is_available: this.parseBoolean(row['Available']),
        is_recommended: this.parseBoolean(row['Recommended']),
        is_spicy: this.parseBoolean(row['Spicy']),
        is_vegetarian: this.parseBoolean(row['Vegetarian'])
      };

      // 获取或创建分类
      let categoryId = categoryMap.get(cleanedData.category);
      if (!categoryId) {
        // 自动创建分类
        const [result] = await connection.execute(
          'INSERT INTO menu_categories (name, name_en, is_active) VALUES (?, ?, TRUE)',
          [cleanedData.category, cleanedData.category]
        );
        categoryId = result.insertId;
        categoryMap.set(cleanedData.category, categoryId);
      }

      // 生成规范化名称
      const normalizedName = this.normalize(cleanedData.name_en);

      // 检查是否存在（按ID或规范化名称）
      let existingItem = null;
      if (cleanedData.id) {
        const [rows] = await connection.execute(
          'SELECT * FROM menu_items WHERE id = ? AND deleted_at IS NULL',
          [cleanedData.id]
        );
        existingItem = rows[0];
      } else {
        const [rows] = await connection.execute(
          'SELECT * FROM menu_items WHERE normalized_name = ? AND deleted_at IS NULL',
          [normalizedName]
        );
        existingItem = rows[0];
      }

      if (existingItem) {
        // 更新现有记录
        await connection.execute(`
          UPDATE menu_items SET 
            name = ?, name_en = ?, normalized_name = ?, description = ?,
            main_ingredients = ?, ingredients = ?, quantity_selection = ?, price = ?, category_id = ?,
            is_available = ?, is_recommended = ?, is_spicy = ?, is_vegetarian = ?,
            updated_at = CURRENT_TIMESTAMP
          WHERE id = ?
        `, [
          cleanedData.name, cleanedData.name_en, normalizedName, cleanedData.description,
          cleanedData.main_ingredients, 
          cleanedData.main_ingredients ? JSON.stringify(cleanedData.main_ingredients.split(',').map(s => s.trim())) : null,
          cleanedData.quantity_selection, cleanedData.price, categoryId,
          cleanedData.is_available, cleanedData.is_recommended, cleanedData.is_spicy, cleanedData.is_vegetarian,
          existingItem.id
        ]);
      } else {
        // 创建新记录
        await connection.execute(`
          INSERT INTO menu_items (
            name, name_en, normalized_name, description, main_ingredients, ingredients,
            quantity_selection, price, category_id, is_available, is_recommended, 
            is_spicy, is_vegetarian
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, [
          cleanedData.name, cleanedData.name_en, normalizedName, cleanedData.description,
          cleanedData.main_ingredients,
          cleanedData.main_ingredients ? JSON.stringify(cleanedData.main_ingredients.split(',').map(s => s.trim())) : null,
          cleanedData.quantity_selection, cleanedData.price, categoryId,
          cleanedData.is_available, cleanedData.is_recommended, cleanedData.is_spicy, cleanedData.is_vegetarian
        ]);
      }

      // 处理肉类选择
      await this.processMeatSelections(connection, existingItem?.id, [
        row['Meat Selection 1'],
        row['Meat Selection 2'], 
        row['Meat Selection 3']
      ]);

    } catch (error) {
      logger.error('处理行数据失败:', error);
      throw error;
    }
  }

  /**
   * 处理肉类选择
   */
  async processMeatSelections(connection, menuItemId, meatSelections) {
    if (!menuItemId) return;

    // 清除现有的肉类选项
    await connection.execute(
      'DELETE FROM menu_options WHERE menu_item_id = ? AND option_group = "Meat"',
      [menuItemId]
    );

    // 添加新的肉类选项
    for (let i = 0; i < meatSelections.length; i++) {
      const meat = this.cleanData(meatSelections[i]);
      if (meat) {
        await connection.execute(`
          INSERT INTO menu_options (menu_item_id, option_group, option_name, sort_order)
          VALUES (?, 'Meat', ?, ?)
        `, [menuItemId, meat, i + 1]);
      }
    }
  }

  /**
   * 解析布尔值（使用MenuValidation工具类）
   */
  parseBoolean(value) {
    return MenuValidation.parseBoolean(value);
  }

  /**
   * 创建快照
   */
  async createSnapshot(connection) {
    try {
      const [menuItems] = await connection.execute(
        'SELECT * FROM menu_items WHERE deleted_at IS NULL'
      );
      const [menuOptions] = await connection.execute(
        'SELECT * FROM menu_options WHERE deleted_at IS NULL'
      );
      const [menuCategories] = await connection.execute(
        'SELECT * FROM menu_categories WHERE deleted_at IS NULL'
      );

      return {
        timestamp: new Date().toISOString(),
        menu_items: menuItems,
        menu_options: menuOptions,
        menu_categories: menuCategories
      };
    } catch (error) {
      logger.error('创建快照失败:', error);
      throw error;
    }
  }

  /**
   * 生成V2导入模板（新三表结构：Dishes/Variants/Options）
   * 返回已写入磁盘的模板文件绝对路径
   */
  async generateTemplateV2() {
    try {
      logger.info('开始生成V2菜单导入模板');

      const wb = XLSX.utils.book_new();

      const addSheet = (name, headers) => {
        const aoa = [headers];
        const ws = XLSX.utils.aoa_to_sheet(aoa);
        // 设置列宽，便于查看
        ws['!cols'] = headers.map(h => ({ wch: Math.min(Math.max(String(h).length + 2, 12), 40) }));
        XLSX.utils.book_append_sheet(wb, ws, name);
      };

      addSheet('Dishes', SCHEMA.Dishes);
      addSheet('Variants', SCHEMA.Variants);
      addSheet('Options', SCHEMA.Options);

      // 写入到临时目录，返回绝对路径
      const outDir = path.join(__dirname, '../../tmp');
      await fs.mkdir(outDir, { recursive: true });
      const filePath = path.join(outDir, `menu_template_v2_${Date.now()}.xlsx`);
      XLSX.writeFile(wb, filePath);

      logger.info(`V2菜单导入模板已生成: ${filePath}`);
      return filePath;
    } catch (error) {
      logger.error('生成V2模板失败:', error);
      throw error;
    }
  }

  async validateV2(job) {
    try {
      logger.info(`V2验证开始，任务ID: ${job.job_id}`);
      await job.updateStatus('validating');

      const wb = XLSX.readFile(job.file_path);
      const requiredSheets = ['Dishes','Variants','Options'];
      const errors = [];
      const warnings = [];
      const normalizationLog = [];

      // Sheet presence
      for (const s of requiredSheets) {
        if (!wb.SheetNames.includes(s)) {
          errors.push({ sheet: s, row_number: 1, field: 'sheet', value: '', error_code: 'MISSING_SHEET', message: `缺少Sheet: ${s}` });
        }
      }

      const readSheet = (name) => {
        const ws = wb.Sheets[name];
        if (!ws) return { headers: [], rows: [] };
        const aoa = XLSX.utils.sheet_to_json(ws, { header: 1, defval: '' });
        if (!aoa.length) return { headers: [], rows: [] };
        const headers = aoa[0].map(h => String(h));
        const rows = aoa.slice(1).filter(r => (r.some(cell => String(cell).trim() !== '')));
        return { headers, rows };
      };

      const dishes = readSheet('Dishes');
      const variants = readSheet('Variants');
      const options = readSheet('Options');

      // Header strictness
      for (const [name, data] of [['Dishes', dishes], ['Variants', variants], ['Options', options]]) {
        const check = exactHeaderMatch(name, data.headers);
        if (!check.match) {
          if (check.missing) check.missing.forEach(col => errors.push({ sheet: name, row_number: 1, field: col, value: '', error_code: 'MISSING_COLUMN', message: `缺少列: ${col}` }));
          if (check.unexpected) check.unexpected.forEach(col => errors.push({ sheet: name, row_number: 1, field: col, value: col, error_code: 'UNEXPECTED_COLUMN', message: `意外列: ${col}` }));
          if (check.order_diff) check.order_diff.forEach(d => errors.push({ sheet: name, row_number: 1, field: 'header', value: d.actual, error_code: 'WRONG_ORDER', message: `第${d.index}列应为 ${d.expected} 实际为 ${d.actual}` }));
        }
      }

      // 若表头不匹配，立即输出报告并结束
      const dirEarly = path.join(path.dirname(job.file_path));
      if (errors.length) {
        const summary = { inserted: 0, updated: 0, skipped: 0, error_count: errors.length };
        const resultEarly = {
          success: false,
          phase: 'validate',
          summary: summary,
          errors: errors,
          warnings: warnings,
          status: 'failed',
          normalization_log: `normalization_log_${job.job_id}.json`,
          category_upsert: `category_upsert_${job.job_id}.json`
        };
        
        // 保存错误报告文件
        const jsonPathEarly = path.join(dirEarly, `validation_report_${job.job_id}.json`);
        const csvPathEarly = path.join(dirEarly, `validate_report_${job.job_id}.csv`);
        const normPathEarly = path.join(dirEarly, `normalization_log_${job.job_id}.json`);
        const catUpsertPathEarly = path.join(dirEarly, `category_upsert_${job.job_id}.json`);
        
        await fs.writeFile(normPathEarly, JSON.stringify([], null, 2), 'utf8');
        await fs.writeFile(catUpsertPathEarly, JSON.stringify({ created: [], existed: [] }, null, 2), 'utf8');
        await fs.writeFile(jsonPathEarly, JSON.stringify(resultEarly, null, 2), 'utf8');
        
        // 生成CSV错误报告
        const csvHeader = 'row_number,sheet,field,value,error_code,message\n';
        const csvLines = errors.map(e => `${e.row_number || ''},${e.sheet || ''},"${e.field || ''}","${(e.value||'').toString().replace(/"/g,'""')}",${e.error_code || ''},"${(e.message||'').toString().replace(/"/g,'""')}"`).join('\n');
        await fs.writeFile(csvPathEarly, csvHeader + csvLines, 'utf8');
        
        // 更新任务状态，保存完整的错误信息
        await job.updateStatus('failed', { 
          validation_report_json: jsonPathEarly, 
          validation_report_csv: csvPathEarly, 
          normalization_log_json: normPathEarly, 
          category_upsert_json: catUpsertPathEarly,
          validation_errors: JSON.stringify(resultEarly)
        });
        
        logger.info(`V2验证结束（表头失败），任务ID: ${job.job_id}，错误数: ${errors.length}`);
        return resultEarly;
      }

      const normalizeRow = (headers, row) => {
        const obj = {}; headers.forEach((h,i)=> obj[h] = row[i] ?? ''); return obj;
      };

      const dishObjs = dishes.rows.map(r => normalizeRow(dishes.headers, r));
      const variantObjs = variants.rows.map(r => normalizeRow(variants.headers, r));
      const optionObjs = options.rows.map(r => normalizeRow(options.headers, r));

      // Field validations and normalization
      const categories = await MenuCategory.findAll({ is_active: true });
      const catNames = new Set(categories.flatMap(c => [String(c.name||'').trim(), String(c.name_en||'').trim()]));

      const categorySeenSet = new Set();
      const skuSet = new Set();
      const slugCountMap = new Map();

      for (let i = 0; i < dishObjs.length; i++) {
        const row = dishObjs[i];
        const rowNum = i + 2;
        // gather categories
        const beforeCat = String(row.category || '').toString();
        const rawCat = String(row.category || '').trim();
        if (beforeCat !== rawCat) {
          normalizationLog.push({ sheet:'Dishes', row: rowNum, field:'category', from: beforeCat, to: rawCat, reason: 'TRIM' });
          row.category = rawCat;
        }
        if (rawCat) categorySeenSet.add(rawCat);

        for (const col of SCHEMA.Dishes) {
          const valRaw = row[col];
          const val = String(valRaw ?? '').trim();
          const rule = RULES.Dishes[col];
          if (col === 'dish_slug' && !val) {
            const auto = slugify(row.name_en || row.name_zh);
            if (!auto) {
              errors.push({ sheet:'Dishes', row_number: rowNum, field:'dish_slug', value:'', error_code:'AUTO_SLUG_FAILED', message:'无法从 name_en 生成 dish_slug' });
            } else {
              normalizationLog.push({ sheet:'Dishes', row: rowNum, field:'dish_slug', from: '', to: auto, reason: 'AUTO_GENERATED' });
              row.dish_slug = auto;
            }
            continue;
          }
          if (col === 'base_status') {
            const norm = normalizeBaseStatus(valRaw);
            if (norm) {
              if (norm !== val) normalizationLog.push({ sheet:'Dishes', row: rowNum, field:'base_status', from: valRaw, to: norm, reason: 'NORMALIZED' });
              row.base_status = norm;
            } else {
              errors.push({ sheet:'Dishes', row_number: rowNum, field:'base_status', value: valRaw, error_code:'VALUE_INVALID', message:'允许值: active, inactive；可自动识别: on/off/true/false/1/0/上架/下架' });
            }
            continue;
          }
          if (col === 'popularity') {
            const { value: pval, mapped } = normalizePopularity(valRaw);
            if (Number.isNaN(pval)) {
              errors.push({ sheet:'Dishes', row_number: rowNum, field:'popularity', value: valRaw, error_code:'TYPE_OR_RANGE', message:'popularity 必须是 0–100 的整数，或可用标签: hot/normal/new（热门/一般/新品）' });
            } else if (pval !== null) {
              if (String(pval) !== String(valRaw)) normalizationLog.push({ sheet:'Dishes', row: rowNum, field:'popularity', from: valRaw, to: pval, reason: mapped ? 'MAPPED' : 'NUMERIC_STRING' });
              if (!Number.isInteger(pval) || pval < 0 || pval > 100) {
                errors.push({ sheet:'Dishes', row_number: rowNum, field:'popularity', value: pval, error_code:'TYPE_OR_RANGE', message:'popularity 必须在 0–100 之间' });
              } else {
                row.popularity = pval;
              }
            } else {
              row.popularity = null; // empty -> null
            }
            continue;
          }
          if (rule?.required && !val) {
            errors.push({ sheet:'Dishes', row_number: rowNum, field: col, value: val, error_code:'REQUIRED', message:`${col} 必填` });
          }
          if (rule?.max && val && val.length > rule.max) {
            errors.push({ sheet:'Dishes', row_number: rowNum, field: col, value: val, error_code:'TOO_LONG', message:`${col} 长度不可超过 ${rule.max}` });
          }
          if (rule?.pattern && val && !rule.pattern.test(val)) {
            errors.push({ sheet:'Dishes', row_number: rowNum, field: col, value: val, error_code:'PATTERN', message:`${col} 格式不合法` });
          }
          if (col === 'tags') {
            const tags = String(row.tags||'').split(',').map(s=>s.trim()).filter(Boolean);
            const uniqTags = Array.from(new Set(tags));
            const processed = uniqTags.join(',');
            if (processed.length && processed.length > 255) {
              errors.push({ sheet:'Dishes', row_number: rowNum, field:'tags', value: row.tags, error_code:'TOO_LONG', message:'tags 处理后总长度超过 255' });
            } else if (processed !== String(row.tags||'')) {
              normalizationLog.push({ sheet:'Dishes', row: rowNum, field:'tags', from: row.tags, to: processed, reason: 'TRIM_DEDUP' });
              row.tags = processed;
            }
          }
        }
        // dish_slug 去重（文件内），重复则追加 -N 后缀
        const currSlug = String(row.dish_slug || '').trim();
        if (currSlug) {
          const base = currSlug;
          const count = (slugCountMap.get(base) || 0) + 1;
          slugCountMap.set(base, count);
          if (count > 1) {
            let newSlug = `${base}-${count}`;
            if (newSlug.length > 64) newSlug = newSlug.slice(0, 64);
            normalizationLog.push({ sheet:'Dishes', row: rowNum, field:'dish_slug', from: currSlug, to: newSlug, reason: 'DEDUP_SUFFIX' });
            row.dish_slug = newSlug;
          }
        }
        // category existence -> 不阻断，通过 category_upsert 产物体现
        const cat = String(row.category||'').trim();
        if (cat && !catNames.has(cat)) {
          // 不再返回 CATEGORY_NOT_EXISTS 警告，交由 category_upsert.json 体现
        }
        // dish_sku uniqueness in file
        const sku = String(row.dish_sku||'').trim();
        if (sku) {
          if (skuSet.has(sku)) errors.push({ sheet:'Dishes', row_number: rowNum, field:'dish_sku', value: sku, error_code:'DUPLICATE', message:'dish_sku 在文件内重复' });
          skuSet.add(sku);
        }
      }

      // Variants
      for (let i = 0; i < variantObjs.length; i++) {
        const row = variantObjs[i];
        const rowNum = i + 2;
        const sku = String(row.dish_sku||'').trim();
        if (!sku) errors.push({ sheet:'Variants', row_number: rowNum, field:'dish_sku', value: '', error_code:'REQUIRED', message:'dish_sku 必填' });
        if (sku && !skuSet.has(sku)) errors.push({ sheet:'Variants', row_number: rowNum, field:'dish_sku', value: sku, error_code:'FK', message:'未知的 dish_sku（未在 Dishes 中声明）' });
        for (const col of SCHEMA.Variants) {
          const val = String(row[col] ?? '').trim();
          const rule = RULES.Variants[col];
          if (!rule) continue;
          if (rule.required && !val) errors.push({ sheet:'Variants', row_number: rowNum, field: col, value: val, error_code:'REQUIRED', message:`${col} 必填` });
          if (rule.max && val && val.length > rule.max) errors.push({ sheet:'Variants', row_number: rowNum, field: col, value: val, error_code:'TOO_LONG', message:`${col} 长度不可超过 ${rule.max}` });
          if (rule.number && val !== '' && !(Number(val) >= (rule.min ?? -Infinity))) errors.push({ sheet:'Variants', row_number: rowNum, field: col, value: val, error_code:'NUMBER', message:`${col} 必须为数字且 ≥ ${rule.min ?? 0}` });
          if (rule.int && val !== '') {
            const n = Number(val);
            if (!Number.isInteger(n) || (rule.min != null && n < rule.min)) {
              const minPart = (rule.min != null) ? ` 且 ≥ ${rule.min}` : '';
              errors.push({ sheet:'Variants', row_number: rowNum, field: col, value: val, error_code:'INTEGER', message: `${col} 必须为整数${minPart}` });
            }
          }
          if (rule.boolean && val !== '' && parseBoolean(val) === undefined) errors.push({ sheet:'Variants', row_number: rowNum, field: col, value: val, error_code:'BOOLEAN', message:`${col} 必须是布尔值` });
        }
      }

      // Options
      for (let i = 0; i < optionObjs.length; i++) {
        const row = optionObjs[i];
        const rowNum = i + 2;
        const sku = String(row.dish_sku||'').trim();
        if (!sku) errors.push({ sheet:'Options', row_number: rowNum, field:'dish_sku', value: '', error_code:'REQUIRED', message:'dish_sku 必填' });
        if (sku && !skuSet.has(sku)) errors.push({ sheet:'Options', row_number: rowNum, field:'dish_sku', value: sku, error_code:'FK', message:'未知的 dish_sku（未在 Dishes 中声明）' });
        for (const col of SCHEMA.Options) {
          const val = String(row[col] ?? '').trim();
          const rule = RULES.Options[col];
          if (!rule) continue;
          if (rule.required && !val) errors.push({ sheet:'Options', row_number: rowNum, field: col, value: val, error_code:'REQUIRED', message:`${col} 必填` });
          if (rule.max && val && val.length > rule.max) errors.push({ sheet:'Options', row_number: rowNum, field: col, value: val, error_code:'TOO_LONG', message:`${col} 长度不可超过 ${rule.max}` });
          if (rule.number && val !== '' && isNaN(Number(val))) errors.push({ sheet:'Options', row_number: rowNum, field: col, value: val, error_code:'NUMBER', message:`${col} 必须是数字` });
          if (rule.boolean && val !== '' && parseBoolean(val) === undefined) errors.push({ sheet:'Options', row_number: rowNum, field: col, value: val, error_code:'BOOLEAN', message:`${col} 必须是布尔值` });
        }
      }

      // Build category_upsert plan (no DB writes here)
      const created = [];
      const existed = [];
      for (const name of categorySeenSet) {
        if (catNames.has(name)) existed.push(name); else created.push(name);
      }

      // Reports
      const dir = path.join(path.dirname(job.file_path));
      const jsonPath = path.join(dir, `validation_report_${job.job_id}.json`);
      const csvPath = path.join(dir, `validate_report_${job.job_id}.csv`);
      const normPath = path.join(dir, `normalization_log_${job.job_id}.json`);
      const catUpsertPath = path.join(dir, `category_upsert_${job.job_id}.json`);

      const summary = { 
        inserted: 0, 
        updated: 0, 
        skipped: 0, 
        error_count: errors.length 
      };
      
      const result = {
        success: errors.length === 0,
        phase: 'validate',
        summary: summary,
        errors: errors,
        warnings: warnings,
        status: errors.length === 0 ? 'passed' : 'failed',
        normalization_log: `normalization_log_${job.job_id}.json`,
        category_upsert: `category_upsert_${job.job_id}.json`
      };

      await fs.writeFile(normPath, JSON.stringify(normalizationLog, null, 2), 'utf8');
      await fs.writeFile(catUpsertPath, JSON.stringify({ created, existed }, null, 2), 'utf8');
      await fs.writeFile(jsonPath, JSON.stringify(result, null, 2), 'utf8');
      
      // 只有当有错误时才生成CSV文件
      if (errors.length > 0) {
        const csvHeader = 'row_number,sheet,field,value,error_code,message\n';
        const csvLines = errors.map(e => `${e.row_number || ''},${e.sheet || ''},"${e.field || ''}","${(e.value||'').toString().replace(/"/g,'""')}",${e.error_code || ''},"${(e.message||'').toString().replace(/"/g,'""')}"`).join('\n');
        await fs.writeFile(csvPath, csvHeader + csvLines, 'utf8');
      }

      // 更新任务状态，保存完整的验证结果
      await job.updateStatus(result.status === 'passed' ? 'validated' : 'failed', { 
        validation_report_json: jsonPath, 
        validation_report_csv: errors.length > 0 ? csvPath : null, 
        normalization_log_json: normPath, 
        category_upsert_json: catUpsertPath,
        validation_errors: JSON.stringify(result)
      });

      logger.info(`V2验证结束，任务ID: ${job.job_id}，通过: ${result.status === 'passed'}，错误数: ${errors.length}`);
      return result;
    } catch (error) {
      logger.error('V2验证失败:', error);
      await job.updateStatus('failed', { error_message: error.message });
      throw error;
    }
  }

  async commitV2(job, requestId = null) {
    // 使用模拟数据库而不是真实数据库连接
    const mockDb = require('../config/mockDatabase');
    const errors = [];
    const warnings = [];
    let inserted = 0;
    let updated = 0;
    let skipped = 0;
    
    // 建立SKU到行号的映射，用于错误追踪
    const skuToRowMap = new Map();
    
    try {
      logger.info(`[${requestId}] V2提交开始，任务ID: ${job.job_id}`);
      await job.updateStatus('committing');
      // 模拟数据库不需要事务

      const wb = XLSX.readFile(job.file_path);
      const getRows = (name) => {
        const ws = wb.Sheets[name];
        const aoa = XLSX.utils.sheet_to_json(ws, { header: 1, defval: '' });
        const headers = aoa[0];
        return aoa.slice(1).filter(r => r.some(c => String(c).trim() !== '')).map(r => Object.fromEntries(headers.map((h,i)=>[h, r[i] ?? ''])));
      };
      const dishes = getRows('Dishes');
      const variants = getRows('Variants');
      const options = getRows('Options');

      // Categories - 使用模拟数据库
      const categories = mockDb.mockData.menu_categories.filter(c => c.is_active);
      const catByName = new Map();
      categories.forEach(c => { catByName.set(String(c.name||'').trim(), c.id); catByName.set(String(c.name_en||'').trim(), c.id); });
      const createdCats = new Set();
      const existedCats = new Set();
      const markExistence = (key) => { if (catByName.has(key)) existedCats.add(key); };

      const ensureCategoryId = async (name) => {
        const key = String(name||'').trim();
        if (!key) return null;
        if (catByName.has(key)) { markExistence(key); return catByName.get(key); }
        // 创建缺失分类 - 使用模拟数据库
        const newId = Math.max(...mockDb.mockData.menu_categories.map(cat => cat.id), 0) + 1;
        const newCategory = {
          id: newId,
          name: key,
          name_en: key,
          description: null,
          description_en: null,
          icon: null,
          color: '#FF6B6B',
          is_active: true,
          sort_order: 9999,
          created_at: new Date(),
          updated_at: new Date(),
          deleted_at: null
        };
        mockDb.mockData.menu_categories.push(newCategory);
        catByName.set(key, newId); createdCats.add(key); return newId;
      };

      const uniqueSlugFor = async (base) => {
        let slug = String(base||'').trim();
        if (!slug) slug = 'item';
        const rand = () => Math.random().toString(36).slice(2,6);
        // 尝试避免重名 - 使用模拟数据库
        let attempt = 0;
        while (attempt < 10) {
          const existingItem = mockDb.mockData.menu_items.find(item => 
            item.normalized_name === slug && !item.deleted_at
          );
          if (!existingItem) return slug;
          slug = `${slug}-${rand()}`.slice(0, 64);
          attempt++;
        }
        return slug;
      };

      const skuToItemId = new Map();

      // Upsert Dishes
      for (let i = 0; i < dishes.length; i++) {
        const d = dishes[i];
        const rowNum = i + 2; // Excel行号（从2开始）
        const sku = String(d.dish_sku||'').trim();
        
        if (!sku) {
          skipped++;
          continue;
        }
        
        // 建立SKU到行号的映射
        skuToRowMap.set(sku, rowNum);
        
        try {
          const name_zh = String(d.name_zh||'').trim();
          const name_en = String(d.name_en||'').trim();
          const desc_zh = String(d.description_zh||'').trim();
          const ingred_zh = String(d.ingredients_zh||'').trim();
          const baseStatus = String(d.base_status||'active').trim().toLowerCase();
          const is_available = baseStatus === 'active' ? 1 : 0;
          const category_id = await ensureCategoryId(d.category);
          const baseSlug = String(d.dish_slug||slugify(name_en||name_zh)).trim();
          const uniqueSlug = await uniqueSlugFor(baseSlug);

          // 查找现有菜品
          const existingItem = mockDb.mockData.menu_items.find(item => 
            item.normalized_name === uniqueSlug || item.name_en === name_en
          );
          
          if (existingItem) {
            // 更新现有菜品
            existingItem.name = name_zh;
            existingItem.name_en = name_en;
            existingItem.description = desc_zh;
            existingItem.main_ingredients = ingred_zh;
            existingItem.is_available = is_available;
            existingItem.normalized_name = uniqueSlug;
            existingItem.category_id = category_id;
            existingItem.updated_at = new Date();
            
            skuToItemId.set(sku, existingItem.id);
            updated++;
          } else {
            // 创建新菜品
            const newId = Math.max(...mockDb.mockData.menu_items.map(item => item.id), 0) + 1;
            const newItem = {
              id: newId,
              name: name_zh,
              name_en: name_en,
              description: desc_zh,
              description_en: null,
              price: null,
              category_id: category_id,
              image_url: null,
              is_available: is_available,
              is_featured: false,
              preparation_time: 15,
              calories: null,
              spice_level: 'mild',
              allergens: JSON.stringify([]),
              main_ingredients: ingred_zh,
              nutritional_info: JSON.stringify({}),
              tags: JSON.stringify([]),
              sort_order: 9999,
              normalized_name: uniqueSlug,
              created_at: new Date(),
              updated_at: new Date(),
              deleted_at: null
            };
            
            mockDb.mockData.menu_items.push(newItem);
            skuToItemId.set(sku, newId);
            inserted++;
          }
        } catch (dbError) {
          // 捕获数据库错误并分类
          let errorCode = 'database_error';
          if (dbError.code === '23505' || dbError.code === 'ER_DUP_ENTRY') {
            errorCode = 'unique_violation';
          } else if (dbError.code === '23502' || dbError.code === 'ER_BAD_NULL_ERROR') {
            errorCode = 'not_null_violation';
          } else if (dbError.code === '23503' || dbError.code === 'ER_NO_REFERENCED_ROW') {
            errorCode = 'foreign_key_violation';
          } else if (dbError.code === '22001' || dbError.code === 'ER_DATA_TOO_LONG') {
            errorCode = 'data_truncation';
          }
          
          errors.push({
            sheet: 'DB',
            row_number: rowNum,
            field: 'dish_sku',
            value: sku,
            error_code: errorCode,
            message: `菜品 ${sku} 处理失败: ${dbError.message}`
          });
          
          logger.warn(`[${requestId}] 菜品 ${sku} (行${rowNum}) 处理失败:`, dbError.message);
        }
      }

      // 处理Variants - 使用模拟数据库
      for (const v of variants) {
        const sku = String(v.dish_sku||'').trim();
        const itemId = skuToItemId.get(sku);
        if (!itemId) continue;
        const code = String(v.variant_code||'').trim();
        const nameZh = String(v.name_zh||v.variant_name||'').trim();
        const vname = String(v.variant_name||'').trim();
        const price = v.price === '' ? null : Number(v.price);
        
        // 查找现有variant
        const existingVariant = mockDb.mockData.menu_variants.find(variant => 
          variant.menu_item_id === itemId && 
          (variant.variant_name_en === code || variant.variant_name === vname)
        );
        
        if (existingVariant) {
          // 更新现有variant
          existingVariant.variant_name = nameZh || vname;
          existingVariant.variant_name_en = code || null;
          existingVariant.price = price;
          existingVariant.updated_at = new Date();
        } else {
          // 创建新variant
          const newVariantId = Math.max(...mockDb.mockData.menu_variants.map(v => v.id || 0), 0) + 1;
          mockDb.mockData.menu_variants.push({
            id: newVariantId,
            menu_item_id: itemId,
            variant_name: nameZh || vname,
            variant_name_en: code || null,
            price: price,
            is_default: false,
            sort_order: 9999,
            created_at: new Date(),
            updated_at: new Date(),
            deleted_at: null
          });
        }
      }

      // 处理Options - 使用模拟数据库
      for (const o of options) {
        const sku = String(o.dish_sku||'').trim();
        const itemId = skuToItemId.get(sku);
        if (!itemId) continue;
        const gcode = String(o.group_code||'').trim();
        const vcode = String(o.value_code||'').trim();
        const gname = String(o.group_name||'').trim();
        const vname = String(o.value_name||'').trim();
        const required = parseBoolean(o.required, false) ? 1 : 0;
        const delta = o.price_delta === '' ? 0 : Number(o.price_delta);

        // 查找现有option
        const existingOption = mockDb.mockData.menu_options.find(option => 
          option.menu_item_id === itemId && 
          option.option_group === (gcode || gname) && 
          option.option_value === (vcode || vname)
        );
        
        if (existingOption) {
          // 更新现有option
          existingOption.option_group = gcode || gname;
          existingOption.option_value = vcode || vname;
          existingOption.price_delta = delta;
          existingOption.is_required = required;
          existingOption.updated_at = new Date();
        } else {
          // 创建新option
          const newOptionId = Math.max(...mockDb.mockData.menu_options.map(o => o.id || 0), 0) + 1;
          mockDb.mockData.menu_options.push({
            id: newOptionId,
            menu_item_id: itemId,
            option_group: gcode || gname,
            option_value: vcode || vname,
            price_delta: delta,
            is_required: required,
            sort_order: 9999,
            created_at: new Date(),
            updated_at: new Date(),
            deleted_at: null
          });
        }
      }

      // 模拟数据库不需要写入文件和事务提交
      // 数据已经直接更新到模拟数据库中
      
      const summary = {
        inserted: inserted,
        updated: updated,
        skipped: skipped,
        error_count: errors.length
      };
      
      // 生成错误报告（如果有错误）
      let errorReportPath = null;
      if (errors.length > 0) {
        const outDir = path.join(path.dirname(job.file_path));
        errorReportPath = path.join(outDir, `commit_errors_${job.job_id}.csv`);
        const csvHeader = 'row_number,sheet,field,value,error_code,message\n';
        const csvLines = errors.map(e => `${e.row_number || ''},${e.sheet || ''},"${e.field || ''}","${(e.value||'').toString().replace(/"/g,'""')}",${e.error_code || ''},"${(e.message||'').toString().replace(/"/g,'""')}"`).join('\n');
        await fs.writeFile(errorReportPath, csvHeader + csvLines, 'utf8');
      }
      
      const result = {
        success: errors.length === 0,
        phase: 'commit',
        summary: summary,
        errors: errors,
        warnings: warnings
      };
      
      await job.updateStatus(errors.length === 0 ? 'completed' : 'failed', { 
        processed_records: inserted + updated, 
        category_upsert_json: null, 
        commit_report_csv: null,
        error_report_csv: null,
        validation_errors: JSON.stringify(result)
      });
      
      logger.info(`[${requestId}] V2提交完成，任务ID: ${job.job_id}，插入: ${inserted}，更新: ${updated}，跳过: ${skipped}，错误: ${errors.length}`);
      return result;
    } catch (error) {
      // 模拟数据库不需要rollback
      logger.error(`[${requestId}] V2提交失败:`, error);
      
      // 分类系统错误
      let errorCode = 'system_error';
      if (error.code === '23505' || error.code === 'ER_DUP_ENTRY') {
        errorCode = 'unique_violation';
      } else if (error.code === '23502' || error.code === 'ER_BAD_NULL_ERROR') {
        errorCode = 'not_null_violation';
      } else if (error.code === '23503' || error.code === 'ER_NO_REFERENCED_ROW') {
        errorCode = 'foreign_key_violation';
      } else if (error.code === '22001' || error.code === 'ER_DATA_TOO_LONG') {
        errorCode = 'data_truncation';
      }
      
      const systemError = {
        success: false,
        phase: 'commit',
        summary: { inserted: 0, updated: 0, skipped: 0, error_count: 1 },
        errors: [{
          sheet: 'DB',
          row_number: 0,
          field: 'system',
          value: '',
          error_code: errorCode,
          message: error.message
        }],
        warnings: []
      };
      
      await job.updateStatus('failed', { 
        error_message: error.message,
        validation_errors: JSON.stringify(systemError)
      });
      
      return systemError;
    }
    // 模拟数据库不需要释放连接
  }
}

module.exports = MenuImportService;